﻿using System.Collections.Generic;
using System.Data;
using Eagles.Base.DataBase;
using Eagles.Base.DataBase.Modle;
using Eagles.DomainService.Model.User;
using Eagles.DomainService.Model.Order;
using Eagles.Interface.DataAccess.ScoreAccess;
using MySql.Data.MySqlClient;

namespace Ealges.DomianService.DataAccess.ScoreData
{
    public class ScoreDataAccess : IScoreAccess
    {
        private readonly IDbManager dbManager;

        public ScoreDataAccess(IDbManager dbManager)
        {
            this.dbManager = dbManager;
        }

        public bool AppScoreExchange(TbOrder order, int userScore, int saleCount)
        {
            var commands = new List<TransactionCommand>()
            {
                new TransactionCommand()
                {
                    CommandString = @"insert into eagles.tb_order (OrgId,ProdId,ProdName,OrderStatus,Score,Count,UserId,Address,Province,City,District,CreateTime) value 
(@OrgId,@ProdId,@ProdName,@OrderStatus,@Score,@Count,@UserId,@Address,@Province,@City,@District,@CreateTime) ",
                    Parameter = order
                },
                new TransactionCommand()
                {
                    CommandString = "insert into eagles.tb_user_score_trace (OrgId,UserId,CreateTime,Score,Comment,OriScore) value (@OrgId,@UserId,@CreateTime,@Score,@Comment,@OriScore) ",
                    Parameter = new {OrgId = order.OrgId,UserId = order.UserId,CreateTime = order.CreateTime,Score = order.Score * -1,Comment = "兑换商品积分扣除",OriScore = userScore
                    }
                }
            };
            return dbManager.ExcutedByTransaction(commands);
        }

        public List<TbUserScoreTrace> GetScoreExchangeLs(int userId)
        {
            return dbManager.Query<TbUserScoreTrace>(@"select OrgId,UserId,TraceId,CreateTime,Score,RewardsType,Comment,OriScore from eagles.tb_user_score_trace where UserId = @UserId ", new { UserId =userId});
        }

        public List<TbUserRank> GetUserRank(int appId)
        {
            var sql = @"select a.UserId, a.Name, b.BranchName, a.Score from eagles.tb_user_info a join tb_branch b on a.BranchId = b.BranchId
where a.OrgId = @OrgId order by Score desc";
            return dbManager.Query<TbUserRank>(sql, new { OrgId = appId });
        }

        public List<TbBranchRank> GetBranchRank(int appId, int rankType)
        {
            var sql = "";
            if (rankType == 0)
                sql = @"select a.BranchId, a.BranchName, sum(b.score) Score from tb_branch a join tb_user_info b on a.BranchId = b.BranchId
where a.OrgId = @OrgId group by a.BranchId order by sum(b.score) desc limit 10 "; //总分
            else
                sql = @"select a.BranchId, a.BranchName, truncate(avg(b.score),0) Score from tb_branch a join tb_user_info b on a.BranchId = b.BranchId
where a.OrgId = @OrgId group by a.BranchId order by avg(b.score) desc limit 10 "; //平均分
            return dbManager.Query<TbBranchRank>(sql, new { OrgId = appId });
        }

        public DataTable GetMyRank(int orgId, int userId)
        {
            var sql = @"select b.rownum,b.UserId from (select t.*, @rownum := @rownum + 1 AS rownum from (select @rownum := 0) r, 
(select * from tb_user_info where OrgId = @OrgId order by Score desc) AS t ) AS b where b.UserId = @UserId ";
            MySqlParameter orgParameter = new MySqlParameter("@OrgId", MySqlDbType.Int32);
            orgParameter.Value = orgId;
            MySqlParameter userParameter = new MySqlParameter("@UserId", MySqlDbType.Int32);
            userParameter.Value = userId;
            MySqlParameter[] parameters = {orgParameter, userParameter};
            return dbManager.GetDataSet(sql, parameters);
        }
    }
}